using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Reflection;
using System.Text.RegularExpressions;

namespace DAL
{
    public class DataBase
    {
        #region Fields

        private DbConnection _Connection;
        private DbProviderFactory _ProviderFactory;
        private string _DbName = null;

        public string DbName
        {
            get { return _DbName; }
            set
            {
                _DbName = value;
                try
                {
                    ConnectionStringSettings css = ConfigurationManager.ConnectionStrings[_DbName];
                    if (css == null)
                        throw new Exception(string.Format("Connection {0} not found in config",_DbName ));
                        
                    _ProviderFactory = DbProviderFactories.GetFactory(css.ProviderName);
                    _Connection = _ProviderFactory.CreateConnection();
                    _Connection.ConnectionString = css.ConnectionString;
                }
                catch (Exception exp) { throw new DbException(DbExceptionType.CreateProvider, exp); }

            }
        }
        private string _ConnectionString;
        public string ConnectionString
        {
            get
            {
                return _ConnectionString;
            }
            set
            {

                _ConnectionString = value;
                try
                {

                    _ProviderFactory = DbProviderFactories.GetFactory(Provider);
                    _Connection = _ProviderFactory.CreateConnection();
                    _Connection.ConnectionString = value;
                }
                catch (Exception exp) { throw new DbException(DbExceptionType.CreateProvider, exp); }


            }
        }
        private string _Provider;
        public string Provider
        {
            get
            {
                return _Provider;
            }
            set
            {
                _Provider = value;
            }
        }

        private bool _KeepConnection = false;

       
        #endregion

         
        #region Constructors

       
        
     
        public DataBase() 
        {





            ConnectionName dbn = Reflector.GetClassAttribute<ConnectionName>(this);

            ConnectionString dbcs = Reflector.GetClassAttribute<ConnectionString>(this);

            Provider dbp = Reflector.GetClassAttribute<Provider>(this);

            if (dbcs != null)
            {
                Provider = dbp.Name;
                ConnectionString = dbcs.String;


            }

            else
                if (dbn != null)
                {
                    DbName = dbn.Name;
                }
                else
                {
                    DbName = this.GetType().Name;
                }



            try
            {
                foreach (IChild<DataBase> c in Reflector.GetTypedFields<IChild<DataBase>>(this))
                {

                    c.SetParent(this);
                }

            }
            catch (Exception exp) { throw new DbException(DbExceptionType.InitTables, exp); }

           

            
        }

        #endregion


        #region Connection
             

        public void Open()
        {
            _KeepConnection = true;
            
            try
            {
                _Connection.Open();
            }
            catch(Exception exp)
            {
                throw new DbException(DbExceptionType.OpenConnection , exp);
            }
            
        }
        public void Close()
        {
            _KeepConnection = false;
            try
            {
                if (IsOpen)
                    _Connection.Close();
            }
            catch { }

        }
        public ConnectionState State
        {
            get
            {
                try
                {
                    return _Connection.State;
                }
                catch { }
                return ConnectionState.Broken;
            }
        }
        public bool IsOpen
        {
            get
            {
                return (_Connection.State != System.Data.ConnectionState.Closed && _Connection.State != ConnectionState.Broken);
            }
        }

        #endregion


        #region Commands


        protected DbCommand  CreateCommand(StoredProcedure sp)
        {
            if (_KeepConnection == false)
            {
                try
                {
                    _Connection.Open();
                  
                }
                catch(Exception exp)
                {
                    throw new DbException(DbExceptionType.OpenConnection , exp);
                }
            }

            DbCommand cmd = _ProviderFactory.CreateCommand();
            cmd.CommandType = sp.CommandType;
            cmd.CommandText = sp.CommandText;
            cmd.Connection = _Connection;
            if (sp.CommandTimeout>=0)
            {
                cmd.CommandTimeout  = sp.CommandTimeout;
            }
            cmd.Transaction = _Transaction;


            foreach (FieldWithAttributesInfo<StoredProcedureParametr> f in Reflector.GetFieldsByAttribute<StoredProcedureParametr>(sp.GetType(), typeof(StoredProcedureParametr)))
            {

                DbParameter param = cmd.CreateParameter();
                param.Direction =  f.Attribute.Direction;

                if (string.IsNullOrEmpty(f.Attribute.ParametrName))
                {
                    param.ParameterName = f.FieldInfo.Name;
                }
                else
                {
                    param.ParameterName = f.Attribute.ParametrName;
                }


                param.Value = f.FieldInfo.GetValue(sp);

                cmd.Parameters.Add(param);


            }

            sp.CurCommand = cmd;
          return cmd;
        }
        protected void ReleaseCommand(StoredProcedure sp)
        {
            try
            {

                if (sp.CommandType == CommandType.StoredProcedure)
                {
                    DbCommand cmd = sp.CurCommand;
                    foreach (FieldWithAttributesInfo<StoredProcedureParametr> f in Reflector.GetFieldsByAttribute<StoredProcedureParametr>(sp.GetType(), typeof(StoredProcedureReturnParametr)))
                    {

                        string par =  f.Attribute.ParametrName;
                        if (string.IsNullOrEmpty(par))
                        {
                            par =  f.FieldInfo.Name;
                        }

                        f.FieldInfo.SetValue(sp, cmd.Parameters[par].Value);



                    }
                }

            }
            catch { throw; }
            finally
            {

                if (_KeepConnection == false)
                {
                    try
                    {
                        _Connection.Close();
                    }
                    catch { }
                }
            }
        }
          
            
        public TType ExecuteScalar<TType>(StoredProcedure sp)
        {

            TType ret = default(TType); 
            try
            {
                
                DbCommand cmd = CreateCommand(sp);

               
                ret = (TType)Convert.ChangeType( cmd.ExecuteScalar(), typeof(TType));
                
                
               


            }
            catch (Exception exp) { throw exp; }
            finally
            {
                ReleaseCommand(sp);
            }


            return ret;
        }
               
        public List<TEntity> Execute<TEntity>(StoredProcedure sp, IPager pager )
        {

            Type EntityType = typeof(TEntity);
            bool IsValueType = EntityType.IsValueType || EntityType.IsArray || EntityType == typeof(string);


            List<TEntity> list = new List<TEntity>();
            DbDataReader dr = null;
            try
            {
                DbCommand cmd =CreateCommand(sp);
                
                dr = cmd.ExecuteReader();

                int i = 0;

                int ItemsCount = int.MaxValue;
                if (pager != null)
                {
                    int n = pager.PageIndex * pager.PageSize;

                    for (  i = 0; i < n && dr.Read(); i++) ;


                    ItemsCount = i +  pager.PageSize;
                }

                Type type = typeof(TEntity);
               
                
                sp.MapParams.ItemsFromReaderToEntity(dr, typeof(TEntity));

                if (IsValueType)
                {
                    for (; i < ItemsCount && dr.Read(); i++)
                    {
                        TEntity obj = default(TEntity);

                        if (sp.MapParams.Items.Count >0  )
                        {
                            MapItem m = sp.MapParams.Items[0];
                            if (!dr.IsDBNull(m.Index))
                            {
                                obj =(TEntity) dr[m.Index];
                            }
                           
                          
                        }

                        
                        list.Add(obj);
                    }
                }
                else
                {
                    for (; i < ItemsCount && dr.Read(); i++)
                    {
                        TEntity obj = Activator.CreateInstance<TEntity>();
                        for (int j = 0; j < sp.MapParams.Items.Count; j++)
                        {
                            MapItem m = sp.MapParams.Items[j];
                            if (!dr.IsDBNull(m.Index))
                            {

                                if (m.Property != null)
                                {
                                    m.Property.SetValue(obj, dr[m.Index], null);
                                }

                                else if (m.Field != null)
                                {
                                    m.Field.SetValue(obj, dr[m.Index]);
                                }
                            }

                        }
                        list.Add(obj);
                    }
                }
                for (; dr.Read(); i++) ;

                               

                if ( pager!=null)
                {
                    pager.TotalCount = i; 
                }

            }
            catch (Exception exp)
            {
                

                throw new DbException(DbExceptionType.ExecuteReader, exp);

            }
            finally
            {
                if (dr != null)
                    dr.Close();
                ReleaseCommand(sp);
            }
            return list;
            
        }

        public List<TEntity> Execute<TEntity>(StoredProcedure sp)
        {
            return Execute<TEntity>(sp, null);
        }
       
        public int Execute(StoredProcedure sp)
        {
            int ret = 0;

            try
            {
                
                DbCommand cmd = CreateCommand(sp);

                ret = cmd.ExecuteNonQuery();

               
            }
            catch (Exception exp)
            {


                throw new DbException(DbExceptionType.ExecuteNonQuery, exp);

            }
            finally
            {
                ReleaseCommand(sp);
            }
            return ret;



            
        }


        public TEntity ExecuteOne<TEntity>(StoredProcedure sp)
        {


            List<TEntity> ret = null;

            ret = Execute<TEntity>(sp,null);
            if (ret != null && ret.Count > 0)
                return ret[0];


            return default(TEntity);
        }

        #endregion

        #region TypedCommands
        public  int  Execute(Type tSP, params object [] obj)
        {
            StoredProcedure sp = (StoredProcedure)Activator.CreateInstance(tSP);

            sp.FillFromEntity(obj);

            int ret = Execute(sp);

            sp.FillEntity(obj);

            return ret;
        }

        public List<TEntity> Execute<TEntity>(Type tSP, params object[] obj)
        {
          
            return Execute<TEntity>( tSP, null,  obj);

        }
        public List<TEntity> Execute<TEntity>(Type tSP, IPager pager , params object[] obj)
        {
            StoredProcedure sp = (StoredProcedure)Activator.CreateInstance(tSP);

            sp.FillFromEntity(obj);
            List<TEntity> ret = Execute<TEntity>(sp,pager);
           
            sp.FillEntity(obj);

            if (pager != null)
            {
                int LastPage = (pager.TotalCount - 1) / pager.PageSize;
                if (pager.PageIndex > LastPage)
                {
                    pager.PageIndex = LastPage;

                    sp = (StoredProcedure)Activator.CreateInstance(tSP);

                    sp.FillFromEntity(obj);
                    ret = Execute<TEntity>(sp, pager);

                    sp.FillEntity(obj);
                }
            }
            return ret;
  

        }

        public TEntity ExecuteOne<TEntity>(Type tSP, params object[] obj)
        {
            StoredProcedure sp = (StoredProcedure)Activator.CreateInstance(tSP);

            sp.FillFromEntity(obj);

            TEntity ret = ExecuteOne<TEntity>(sp);

            sp.FillEntity(obj);

            return ret;
        }

        public TType ExecuteScalar<TType>(Type spType, params object[] obj)
        {
            StoredProcedure sp = (StoredProcedure)Activator.CreateInstance(spType);

            sp.FillFromEntity(obj);

            TType ret = ExecuteScalar<TType>(sp);

            sp.FillEntity(obj);

            return ret;
        }
       


        #endregion


        #region Transaction

        private System.Data.Common.DbTransaction _Transaction = null;
        public void TransactionBegin()
        {
            
            _Transaction = _Connection.BeginTransaction();
        }
        public void TransactionBegin(IsolationLevel IsolationLevel)
        {

            _Transaction = _Connection.BeginTransaction(IsolationLevel);
        }
        public void TransactionRollback()
        {
            if(_Transaction != null)
                _Transaction.Rollback();
        }

        public void TransactionCommit()
        {
            if (_Transaction != null)
                _Transaction.Commit();
        }

        #endregion
    }
}
